// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using Kailua.net.windward.utils;

namespace net.windward.utils.ado.PostgreSql
{
	/// <summary>
	/// A PostgreSQL database.
	/// </summary>
	public class WrPostgreSqlDatabase : IWrDatabase
	{
		private readonly DbProviderFactory provider;
		private readonly string server;
		private readonly string database;
		private static readonly Hashtable types;
		private readonly IWrSyntax syntax;
		private WrTable[] tables;
		private WrView [] views;

		static WrPostgreSqlDatabase()
		{
			// list at very end of http://npgsql.projects.postgresql.org/docs/manual/UserManual.html
			types = new Hashtable();
			types.Add("int8", DbType.Int64);
			types.Add("bool", DbType.Boolean);
			types.Add("bytea", DbType.Binary);
			types.Add("date", DbType.Date);
			types.Add("float8", DbType.Double);
			types.Add("int4", DbType.Int32);
			types.Add("money", DbType.Decimal);
			types.Add("numeric", DbType.Decimal);
			types.Add("float4", DbType.Single);
			types.Add("int2", DbType.Int16);
			types.Add("text", DbType.String);
			types.Add("time", DbType.Time);
			types.Add("timetz", DbType.Time);
			types.Add("timestamp", DbType.DateTime);
			types.Add("timestamptz", DbType.DateTime);
			types.Add("varchar", DbType.String);
			types.Add("bit", DbType.Boolean);
			types.Add("uuid", DbType.Guid);
		}

		/// <summary>
		/// Create a PostgreSql database object.
		/// </summary>
		/// <param name="provider">The provider for this server.</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrPostgreSqlDatabase(DbProviderFactory provider, string server, string database)
		{
			this.provider = provider;
			this.server = server;
			this.database = database;
			syntax = new WrPostgreSqlSyntax(provider);
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return null;
			}
		}

		private static DbType ConvertFromPostgreSqlDbType(string name)
		{
			// some don't convert
			string key = name.ToLower();
			if (types.Contains(key))
				return (DbType)types[key];

			try
			{
				return (DbType)Enum.Parse(typeof(DbType), name, true);
			}
			catch (Exception)
			{
				return DbType.Object;
			}
		}

		#region IWrDatabase Members

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return null; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(ConnectionString("postgres", credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			Trap.trap();
			tables = GetTables(connStr, owner);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], connStr, owner);
				tables[index] = new WrTable(tables[index].Schema, tables[index].Name, tables[index].Description, tables[index].IsSystem, columns);
			}

			views = GetViews(connStr, owner);
			for (int index = 0; index < views.Length; index++)
			{
				WrColumn[] columns = GetColumns(views[index], connStr, owner);
				views[index] = new WrView(views[index].Schema, views[index].Name, views[index].Description, views[index].IsSystem, columns);
			}
		}

		/// <summary>
		/// Return all tables in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
		/// <returns>all tables in this database.</returns>
		private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner)
		{

			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
						switch (owner & WrVendor.ELEM_OWNER.ALL)
						{
							case WrVendor.ELEM_OWNER.ALL:
								cmd.CommandText = "SELECT schemaname, tablename from pg_tables order by tablename";
								break;
							case WrVendor.ELEM_OWNER.SYSTEM:
								cmd.CommandText = "SELECT schemaname, tablename from pg_tables where (schemaname = 'pg_catalog') or (schemaname = 'information_schema') order by tablename";
								break;
							default:
								cmd.CommandText = "SELECT schemaname, tablename from pg_tables where (schemaname != 'pg_catalog') and (schemaname != 'information_schema') order by tablename";
								break;
						}
						cmd.Parameters.Add(MakeParameter("db", database));
						ArrayList rtn = new ArrayList();
						using (IDataReader reader = cmd.ExecuteReader())
						{
							while (reader.Read())
							{
								string schema = reader.GetString(0);
								if (schema == "public")
									schema = null;
								rtn.Add(new WrTable(schema, reader.GetString(1), "", false, null));
							}
						}
						rtn.Sort();
						return (WrTable[])rtn.ToArray(typeof(WrTable));
					}
				}
			}
			catch (Exception)
			{
				return new WrTable[0];
			}
		}

		/// <summary>
		/// Return all views in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what views are returned and if metadata is also retrieved.</param>
		/// <returns>all views in this database.</returns>
		private WrView[] GetViews(string connStr, WrVendor.ELEM_OWNER owner)
		{

			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
						switch (owner & WrVendor.ELEM_OWNER.ALL)
						{
							case WrVendor.ELEM_OWNER.ALL:
								cmd.CommandText = "SELECT schemaname, viewname from pg_views order by viewname";
								break;
							case WrVendor.ELEM_OWNER.SYSTEM:
								cmd.CommandText = "SELECT schemaname, viewname from pg_views where (schemaname = 'pg_catalog') or (schemaname = 'information_schema') order by viewname";
								break;
							default:
								cmd.CommandText = "SELECT schemaname, viewname from pg_views where (schemaname != 'pg_catalog') and (schemaname != 'information_schema') order by viewname";
								break;
						}
						ArrayList rtn = new ArrayList();
						using (IDataReader reader = cmd.ExecuteReader())
						{
							while (reader.Read())
							{
								string schema = reader.GetString(0);
								if (schema == "public")
									schema = null;
								rtn.Add(new WrView(schema, reader.GetString(1), "", false, null));
							}
						}
						rtn.Sort();
						return (WrView[])rtn.ToArray(typeof(WrView));
					}
				}
			}
			catch (Exception)
			{
				return new WrView[0];
			}
		}

		/// <summary>
		/// Return all columns in a table or view.
		/// </summary>
		/// <param name="table">The name of the table or view.</param>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <returns>all columns in a table or view.</returns>
		private WrColumn[] GetColumns(WrBaseTable table, string connStr, WrVendor.ELEM_OWNER owner)
		{
			
			try
			{
				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();

					// get the joins
					Hashtable pkMaps = new Hashtable();
					if ((owner & WrVendor.ELEM_OWNER.KEY_JOINS) != 0)
						try
						{
							// get the FK:PK relationships
							using (DbCommand cmd = provider.CreateCommand())
							{
								cmd.Connection = conn;
								cmd.CommandType = CommandType.Text;
								cmd.CommandText = "SELECT schema_name, table_name, fk_info[1] AS FK, fk_info[2] AS PK_table, fk_info[3] AS PK_column FROM " +
									"(SELECT n.nspname AS schema_name, c.relname AS table_name, regexp_matches(pg_catalog.pg_get_constraintdef(r.oid)::text, " +
                                    "E'FOREIGN KEY \\\\((.*)\\\\) REFERENCES (.*?)\\\\((.*?)[,)].*') AS fk_info FROM pg_catalog.pg_constraint r JOIN " +
									"pg_catalog.pg_class c ON (c.relname = :table AND c.oid = r.conrelid AND r.contype = 'f' ) JOIN " +
                                    "pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) ORDER BY 1) AS bar";
								cmd.Parameters.Add(MakeParameter("table", table.FullName));

								using (DbDataReader myReader = cmd.ExecuteReader())
								{
									while (myReader.Read())
									{
										string column = myReader.GetString(2).ToLower().Trim();
										string pk = myReader.GetString(3).Trim() + '.' + myReader.GetString(4).Trim();
										if (!string.IsNullOrEmpty(column))
											pkMaps.Add(column, pk);
									}
								}
							}
						}
						catch (Exception)
						{
							// nada
						}

					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
						cmd.CommandText =
							"SELECT a.attname as colname, t.typname AS type FROM pg_class c, pg_attribute a, pg_type t WHERE " +
							"c.relname = :table AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid";
                        cmd.Parameters.Add(MakeParameter("table", table.FullName));

						ArrayList rtn = new ArrayList();
						using (IDataReader reader = cmd.ExecuteReader())
						{
							while (reader.Read())
							{
								string str = reader.GetString(1).ToLower();
                                if (str.IndexOf('(') != -1)
                                    str = str.Substring(0, str.IndexOf('('));
								DbType typ = ConvertFromPostgreSqlDbType(str);

								string columnName = reader.GetString(0);
								string pk = (string)pkMaps[columnName.ToLower()];

								rtn.Add(new WrColumn(columnName, "", typ, pk));
							}
						}
                     
						return (WrColumn[]) rtn.ToArray(typeof (WrColumn));
					}
				}
			}
			catch (Exception)
			{
				return new WrColumn[0];
			}
		}

		#endregion

		private string ConnectionString(string dbName, WrCredentials credentials)
		{
			return WrPostgreSqlVendor.ConnectionString(provider, server, database, credentials, true);
		}

		private void SetParameter(DbCommand cmd, object value)
		{
			if (value == null)
				value = DBNull.Value;

			DbParameter param = provider.CreateParameter();
			param.ParameterName = "?";
			param.Value = value;

			cmd.Parameters.Add(param);
		}

        private DbParameter MakeParameter(string name, object value)
        {
            if (value == null)
                value = DBNull.Value;

            DbParameter param = provider.CreateParameter();
            param.ParameterName = name;
            param.Value = value;

            return param;
        }
	}
}
